const connection = require("../app/database")

class MomentService {
  // 新增动态
  async create(content, id) {
    // 预处理语句
    const statement = `INSERT INTO moment (content, user_id) VALUES (?, ?);`
    // 执行sql语句
    const [result] = await connection.execute(statement, [content, id])
    return result
  }

  // 删除动态
  async remove(momentId) {
    const statement = `DELETE FROM moment WHERE id = ?;`
    const [result] = await connection.execute(statement, [momentId])
    return result
  }

  // 查询动态列表
  async queryList(size = 10, offset = 0) {
    const statement = `SELECT m.id id, m.content content, m.createAt createTime, m.updateAt updateTime, JSON_OBJECT('id', u.id, 'username', u.username, 'createTime', u.createAt, 'updateTime', u.updateAt) user, (SELECT COUNT(*) FROM comment WHERE moment_id = m.id) commentCount FROM moment m LEFT JOIN user u ON u.id = m.user_id LIMIT ? OFFSET ?;`
    // String()来转化为字符串形式，防止传入数字类型数据库操作失败，貌似limit和offset必须为字符串
    const [result] = await connection.execute(statement, [
      String(size),
      String(offset),
    ])
    return result
  }

  // 查询动态详情
  async queryById(momentId) {
    const statement = `SELECT m.id id, m.content content, m.createAt createTime, m.updateAt updateTime,
    JSON_OBJECT
      ('id', u.id, 'username', u.username, 'createTime', u.createAt, 'updateTime', u.updateAt) user,
      (
        JSON_ARRAYAGG(
          JSON_OBJECT('id', c.id, 'content', c.content, 'commentId', c.comment_id, 'user', JSON_OBJECT(			 'name', cu.username, 'id', cu.id))
        )
      ) comments
    FROM moment m 
    LEFT JOIN user u ON u.id = m.user_id
    LEFT JOIN comment c ON c.moment_id = m.id
    LEFT JOIN user cu ON cu.id = c.user_id
    WHERE m.id = ?
    GROUP BY m.id;`
    const [result] = await connection.execute(statement, [momentId])
    return result
  }

  // 更新动态
  async update(content, momentId) {
    const statement = `UPDATE moment SET content = ? WHERE id = ?;`
    const [result] = await connection.execute(statement, [content, momentId])
    return result
  }
}

module.exports = new MomentService()
